﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.Linq;
using System.Text;
using System.Xml;
using Common.Utilities.OpenXml;

namespace Common.Utilities
{
    /// <summary>
    /// 
    /// </summary>
    [Serializable]
    public class SetDataProvider
    {
        private string _SetName;
        /// <summary></summary>
        public string SetName
        {
            get
            {
                return this._SetName;
            }
            set
            {
                this._SetName = value;
            }
        }

        private SetDataSourceType _SourceType;
        /// <summary>either from db or from xml</summary>
        public SetDataSourceType SourceType
        {
            get
            {
                return this._SourceType;
            }
            set
            {
                this._SourceType = value;
            }
        }

        private string _DataSourcePath;
        /// <summary>
        /// DB: connection string 
        /// XML: xml file path
        /// </summary>
        public string DataSourcePath
        {
            get
            {
                return this._DataSourcePath;
            }
            set
            {
                this._DataSourcePath = value;
            }
        }

        private string _RecordListPath;
        /// <summary>
        /// DB: select sql statement
        /// XML: xPath to select xml node list that each node represent a record
        /// </summary>
        public string RecordListPath
        {
            get
            {
                return this._RecordListPath;
            }
            set
            {
                this._RecordListPath = value;
            }
        }

        private string _KeyFieldName;
        /// <summary></summary>
        public string KeyFieldName
        {
            get
            {
                return this._KeyFieldName;
            }
            set
            {
                this._KeyFieldName = value;
            }
        }

		private string _LabelField;
		/// <summary></summary>
		public string LabelField
		{
			get
			{
				return this._LabelField;
			}
			set
			{
				this._LabelField = value;
			}
		}

        private Dictionary<string, string> _FieldPaths;
        /// <summary>
        /// DB: field label -> key column name
        /// XML: field label -> xpath to select key
        /// tgt field -> src field
        /// </summary>
        public Dictionary<string, string> FieldPaths
        {
            get
            {
                return this._FieldPaths;
            }
            set
            {
                this._FieldPaths = value;
            }
        }

        /// <summary>
        /// 
        /// </summary>
        /// <returns></returns>
        public DataTable GetDataFromProvider()
        {
            DataTable dt = new DataTable(this.SetName);
            if (this.SourceType == SetDataSourceType.DB)
            {
                SqlConnection conn = new SqlConnection(this.DataSourcePath);
                try
                {
                    conn.Open();
                    SqlCommand cmd = conn.CreateCommand();
                    cmd.CommandType = CommandType.Text;
                    string sql = string.Empty;
                    foreach (string columnName in this.FieldPaths.Values)
                    {
                        if (columnName != this.KeyFieldName)
                        {
                            if (sql.Length > 0)
                                sql += ",";
                            sql += "[" + columnName + "]";
                        }
                    }
                    if (sql.Length > 0)
                        cmd.CommandText = string.Format("select [{0}], {1} from [{2}]", this.KeyFieldName, sql,
                                                        this.RecordListPath);
                    else
                        cmd.CommandText = string.Format("select [{0}] from [{1}]", this.KeyFieldName, this.RecordListPath);
                    SqlDataAdapter dataAdapter = new SqlDataAdapter(cmd);
                    DataSet ds = new DataSet();
                    dataAdapter.Fill(ds, this.SetName);
                    dt = ds.Tables[0].Copy();
                }
                catch (Exception ex)
                {
                    throw new Exception("Unable to get data for " + this.SetName + ": " + ex.ToString());
                }
                finally
                {
                    conn.Close();
                }
            }
            else if (this.SourceType == SetDataSourceType.XML)
            {
                XmlDocument xDoc = new XmlDocument();
                xDoc.Load(this.DataSourcePath);
                XmlNodeList xNodes = xDoc.SelectNodes(this.RecordListPath);
                if (xNodes != null && xNodes.Count > 0)
                {
                    XmlNode firstNode = xNodes[0];
                    foreach (XmlAttribute attr in firstNode.Attributes)
                    {
                        dt.Columns.Add(attr.Name, typeof(string));
                    }
                    foreach (XmlNode xNode in xNodes)
                    {
                        DataRow dr = dt.NewRow();
                        foreach (XmlAttribute attr in xNode.Attributes)
                        {
                            if (dt.Columns.Contains(attr.Name))
                                dr[attr.Name] = attr.Value;
                        }
                        dt.Rows.Add(dr);
                    }
                }
                else
                {
                    throw new Exception("Unable to map to fields from xml file");
                }
            }
            else if (this.SourceType == SetDataSourceType.ExcelFile)
            {
                dt = ExcelReader.ReadTable(this.DataSourcePath, this.RecordListPath);
                // dt = ExcelFileHelper.ReadWorksheetData(this.DataSourcePath, this.RecordListPath);
                dt.TableName = this.SetName;
            }

            return dt;
        }

        /// <summary>
        /// 
        /// </summary>
        /// <param name="xDoc"></param>
        /// <param name="providerNode"></param>
        public void Save(ref XmlDocument xDoc, XmlNode providerNode)
        {
            XmlDataUtil.UpdateAttribute(ref xDoc, providerNode, "SetName",this.SetName);
            XmlDataUtil.UpdateAttribute(ref xDoc, providerNode, "SrcType", this.SourceType.ToString());
            XmlDataUtil.UpdateAttribute(ref xDoc, providerNode, "DataSrcPath", this.DataSourcePath);
            XmlDataUtil.UpdateAttribute(ref xDoc, providerNode, "RecordListPath", this.RecordListPath);
            XmlDataUtil.UpdateAttribute(ref xDoc, providerNode, "KeyFieldName", this.KeyFieldName);
            XmlDataUtil.UpdateAttribute(ref xDoc, providerNode, "LabelFieldName", this.LabelField);
            XmlNode mappingNodes = XmlDataUtil.AddElement(ref xDoc, providerNode, "FieldMappings");
            if(this.FieldPaths !=null && this.FieldPaths.Count>0)
            {
                foreach(string tgtField in this.FieldPaths.Keys)
                {
                    XmlNode mappingNode = XmlDataUtil.AddElement(ref xDoc, mappingNodes, "FieldMapping");
                    XmlDataUtil.UpdateAttribute(ref xDoc, mappingNode,"TgtField", tgtField);
                    XmlDataUtil.UpdateAttribute(ref xDoc, mappingNode,"SrcField",this.FieldPaths[tgtField]);
                }
            }
        }

        /// <summary>
        /// 
        /// </summary>
        /// <param name="providerNode"></param>
        /// <returns></returns>
        public static SetDataProvider Read(XmlNode providerNode)
        {
            SetDataProvider provider=new SetDataProvider();
            provider.SetName = XmlDataUtil.GetAttributeValue(providerNode, "SetName", "");
            provider.SourceType =
                (SetDataSourceType) Enum.Parse(
                                        typeof (SetDataSourceType),
                                        XmlDataUtil.GetAttributeValue(
                                            providerNode, "SrcType", SetDataSourceType.DB.ToString()));
            provider.DataSourcePath = XmlDataUtil.GetAttributeValue(providerNode, "DataSrcPath", "");
            provider.RecordListPath = XmlDataUtil.GetAttributeValue(providerNode, "RecordListPath", "");
            provider.KeyFieldName = XmlDataUtil.GetAttributeValue(providerNode, "KeyFieldName", "");
            provider.LabelField = XmlDataUtil.GetAttributeValue(providerNode, "LabelFieldName", "");
            provider.FieldPaths=new Dictionary<string, string>();
            XmlNodeList mappingNodes = providerNode.SelectNodes("FieldMappings/FieldMapping");
            if(mappingNodes !=null && mappingNodes.Count>0)
            {
                foreach(XmlNode mappingNode in mappingNodes)
                {
                    string tgtField = XmlDataUtil.GetAttributeValue(mappingNode, "TgtField", "");
                    string srcField = XmlDataUtil.GetAttributeValue(mappingNode, "SrcField", "");
                    provider.FieldPaths.Add(tgtField,srcField);
                }
            }
            return provider;
        }
    }
}
